package org.zjvis.datascience.web.controller;

import cn.weiguangfu.swagger2.plus.annotation.ApiPlus;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import java.io.IOException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.zjvis.datascience.common.constant.SqlTemplate;
import org.zjvis.datascience.common.model.ApiResult;
import org.zjvis.datascience.common.model.ApiResultCode;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.RestTemplateUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.DataPreviewVO;
import org.zjvis.datascience.common.vo.RecommendVO;
import org.zjvis.datascience.service.MLModelService;
import org.zjvis.datascience.service.RecommendService;
import org.zjvis.datascience.service.UDFService;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @description 推荐管理接口 Controller
 * @date 2021-12-27
 */
@ApiPlus(value = true)
@RestController
@RequestMapping("/recommend")
@Api(tags = "recommend", description = "推荐管理接口")
@Validated
public class RecommendController {

    @Autowired
    private RecommendService recommendService;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private MLModelService mlModelService;

    @PostMapping(value = "/preCalculate")
    @ResponseBody
    @ApiOperation(value = "推荐预计算", notes = "推荐预计算")
    public ApiResult<RecommendVO> preCalculate(HttpServletRequest request, @RequestBody JSONObject param) {
        if (null == param.getString("tableName") || null == param.getString("fields") || null == param.getString("method")) {
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR);
        }
        RecommendVO vo = null;
        try {
            vo = recommendService.preCalculate(param);
        } catch (Exception e) {
            return ApiResult.valueOf(ApiResultCode.SYS_ERROR);
        }
        return ApiResult.valueOf(vo);
    }

    @PostMapping(value = "/viewData")
    @ResponseBody
    @ApiOperation(value = "数据查看", notes = "数据查看")
    public ApiResult<List<DataPreviewVO>> viewData(HttpServletRequest request, @RequestBody RecommendVO recommendVO) {
        if (StringUtils.isEmpty(recommendVO.getTableNames())) {
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR);
        }
        List<DataPreviewVO> previewVOS = recommendService.viewData(recommendVO);
        return ApiResult.valueOf(previewVOS);
    }

    @PostMapping(value = "/operator")
    @ResponseBody
    @ApiOperation(value = "单独调用算子", notes = "单独调用算子")
    public ApiResult<JSONObject> executeOperator(HttpServletRequest request, @RequestBody JSONObject params) {
        if (!params.containsKey("algName")) {
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR);
        }
        String opSql = "";
        String joinSql = "";
        String joinTable = "";

        String idCol = "_record_id_";
        if (params.containsKey("idCol")) {
            idCol = params.getString("idCol");
        }
        Long timeStamp = System.currentTimeMillis();

        String featureCols = params.getString("features");
        List<String> fields = new ArrayList<>();
        fields.add(String.format("\"%s\"", idCol));
        if (StringUtils.isEmpty(featureCols)) {
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR);
        }
        String[] tmps = featureCols.split(",");
        for (String item : tmps) {
            fields.add(String.format("\"%s\"", item));
        }
        String sampleTable = String.format("pipeline.view_recommend_temp_%s", timeStamp);
        String sourceTable = params.getString("source");
        String resultTable = String.format("pipeline.solid_recommend_sample_%s", timeStamp);
        joinTable = String.format("pipeline.solid_recommend_join_%s", timeStamp);
        String modelTable = String.format("pipeline.solid_recommend_model_%s", timeStamp);
        String residualTable = String.format("pipeline.solid_recommend_residual_%s", timeStamp);
        String summaryTable = String.format("pipeline.solid_recommend_summary_%s", timeStamp);
        int k = 2;
        double proportion = 0.9;
        int isK = 1;
        String sqlTemplate = "select * from \"%s\".\"pca_dense_sample\"('CREATE VIEW %s AS SELECT %s from %s where \"%s\" <= %s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, %s)";
        opSql = String.format(sqlTemplate, SqlTemplate.SCHEMA, sampleTable, Joiner.on(",").join(fields),
                sourceTable, idCol, 5000, modelTable, resultTable,
                residualTable, summaryTable, idCol, featureCols, k, proportion, isK);
        String label = params.getString("label");
        String joinSqlTpl = "create table %s as select a.%s, a.f1, a.f2, b.%s from %s a, %s b where a.%s = b.%s";
        joinSql = String.format(joinSqlTpl, joinTable, idCol, label, resultTable, sourceTable, idCol, idCol);

        if (StringUtils.isNotEmpty(opSql) && StringUtils.isNotEmpty(joinSql) && StringUtils.isNotEmpty(joinTable)) {
            Connection conn = null;
            Statement st;
            try {
                conn = gpDataProvider.getConn(1L);
                st = conn.createStatement();
            } catch (Exception e) {
                JDBCUtil.close(conn, null, null);
                return ApiResult.valueOf(null);
            }
            String output = "";
            int status;
            try {
                ResultSet rs = st.executeQuery(opSql);
                while (rs.next()) {
                    output = rs.getString(1);
                    break;
                }
                JSONObject jsonObject = JSONObject.parseObject(output);
                status = jsonObject.getInteger("status");
                if (status != 0) {
                    return ApiResult.valueOf(null);
                }
                st.execute(joinSql);
            } catch (Exception e) {
                return ApiResult.valueOf(null);
            } finally {
                JDBCUtil.close(conn, st, null);
            }
            RecommendVO vo = new RecommendVO();
            vo.setTableNames(joinTable);
            vo.setName("demo");
            vo.setUserId(JwtUtil.getCurrentUserId());
            JSONObject data = new JSONObject();
            data.put("status", 0);
            data.put("error_msg", "success");
            vo.setData(data);
            Long id = recommendService.save(vo.toTask());
            JSONObject retData = new JSONObject();
            retData.put("id", id);
            retData.put("tableName", joinTable);
            return ApiResult.valueOf(retData);
        }

        return ApiResult.valueOf(null);
    }

    @PostMapping(value = "/computeStats")
    @ResponseBody
    @ApiOperation(value = "数理统计", notes = "数理统计")
    public ApiResult<JSONObject> computeStats(HttpServletRequest request, @RequestBody RecommendVO recommendVO)
        throws IOException {
        if (recommendVO.getTableNames() == null || recommendVO.getData() == null || recommendVO.getData().getString("featureCol") == null) {
            return ApiResult.valueOf(null);
        }
        if (recommendVO.getData().getString("featureCol").equals("")){
            return ApiResult.valueOf(new JSONObject());
        }
        String tableName = recommendVO.getTableNames();
        JSONObject data = recommendVO.getData();
        data.put("source_table", tableName);
        data.put("feature_col", data.getString("featureCol"));
        data.put("apiPath", "statistics");
        if (data.getString("func") == null) {
            data.put("func", "");
        }
        if (data.getString("method") == null) {
            data.put("method", "ind");
        }
        if (data.getString("condition") == null) {
            data.put("condition", "=");
        }
        if (data.getFloat("observed") == null) {
            data.put("observed", 0);
        }
        if (data.getFloat("alpha") == null) {
            data.put("alpha", 0.05f);
        }
        String retStr = mlModelService.submitFlaskJob(data);
        if (null != retStr && !retStr.isEmpty()) {
            JSONObject ret = JSONObject.parseObject(retStr);
            ret.put("feature_col", data.getString("featureCol"));
            return ApiResult.valueOf(ret);
        } else {
            //非数值类型使用数理统计会报错，直接返回空
            return ApiResult.valueOf(new JSONObject());
        }
    }

}
